require('dotenv').config();
const express = require('express');
const mysql = require('mysql2/promise');
const cors = require('cors');
const path = require('path');
const TableAdapter = require('./table-adapter');
const aiService = require('./ai-service');
const merchantService = require('./merchant-service');

const app = express();
// 使用环境变量中的PORT，如果没有设置则默认3002
const port = process.env.PORT || 3002;

// 中间件
app.use(express.json());

// 添加跨域隔离头部以支持SharedArrayBuffer
app.use((req, res, next) => {
  res.setHeader('Cross-Origin-Opener-Policy', 'same-origin');
  res.setHeader('Cross-Origin-Embedder-Policy', 'require-corp');
  res.setHeader('Access-Control-Allow-Origin', '*');
  res.setHeader('Access-Control-Allow-Methods', 'GET, POST, PUT, DELETE, OPTIONS');
  res.setHeader('Access-Control-Allow-Headers', 'Content-Type, Authorization');
  next();
});

// 静态资源服务 - 带错误处理
const staticMiddleware = express.static(path.join(__dirname, '../img'));
app.use('/img', (req, res, next) => {
  staticMiddleware(req, res, (err) => {
    if (err) {
      console.error('静态资源错误:', err);
      return res.status(404).json({
        success: false,
        message: '图片资源不存在或无法访问'
      });
    }
    next();
  });
});

// 数据库配置 - 使用环境变量
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'root',
  database: process.env.DB_NAME || 'community_service_platform',
  port: process.env.DB_PORT || 3306,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  enableKeepAlive: true,
  keepAliveInitialDelay: 30000
};

// 创建数据库连接池
const pool = mysql.createPool(dbConfig);
const tableAdapter = new TableAdapter();

// 统一的API响应处理器
class APIHandler {
  static success(res, data, message = '操作成功') {
    res.json({
      success: true,
      message,
      data
    });
  }

  static error(res, error, statusCode = 500) {
    console.error('API错误:', error);
    res.status(statusCode).json({
      success: false,
      message: error.message || '操作失败',
      error: error.message
    });
  }

  static async handleRequest(res, requestFn) {
    try {
      const result = await requestFn();
      this.success(res, result);
    } catch (error) {
      this.error(res, error);
    }
  }
}

// 数据库查询工具类
class DatabaseService {
  static async query(sql, params = []) {
    const [rows] = await pool.execute(sql, params);
    return rows;
  }

  static async count(tableName, whereConditions = [], params = []) {
    let sql = `SELECT COUNT(*) as total FROM ${tableName}`;
    if (whereConditions.length > 0) {
      sql += ` WHERE ${whereConditions.join(' AND ')}`;
    }
    const [result] = await pool.execute(sql, params);
    return result[0].total;
  }

  static buildPagination(page = 1, limit = 10) {
    const offset = (page - 1) * limit;
    return { limit, offset, page: parseInt(page) };
  }
}

// 初始化数据库连接
async function initializeDatabase() {
  try {
    const connection = await pool.getConnection();
    console.log('✅ 数据库连接成功');
    console.log(`📊 数据库: ${dbConfig.database}`);

    // 初始化表信息
    const idleItemsTable = await tableAdapter.findTableName(connection, 'idleItems');
    const skillsTable = await tableAdapter.findTableName(connection, 'skills');

    console.log('初始化表信息:', idleItemsTable, skillsTable);
    global.tableInfo = {
      idleItems: {
        tableName: idleItemsTable,
        mapping: await tableAdapter.getFieldMapping(connection, idleItemsTable, 'idleItems')
      },
      skills: {
        tableName: skillsTable,
        mapping: await tableAdapter.getFieldMapping(connection, skillsTable, 'skills')
      }
    };

    connection.release();
  } catch (error) {
    console.error('❌ 数据库连接失败:', error.message);
  }
}

// API路由

// 这部分API已被移至文件下方，使用模拟数据实现

// 获取闲置物品列表
app.get('/api/idle-items', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const { page = 1, category, duration } = req.query;
    const { limit, offset } = DatabaseService.buildPagination(page);

    // 使用模拟数据或数据库查询
    if (!global.tableInfo?.idleItems) {
      const mockData = await tableAdapter.createMockData(pool, 'services');
      let filteredData = mockData?.idleItems || [];

      if (category && category !== '全部类别') {
        filteredData = filteredData.filter(item => item.itemCategory === category);
      }
      if (duration && duration !== '') {
        filteredData = filteredData.filter(item => item.borrowDuration === parseInt(duration));
      }

      const total = filteredData.length;
      const paginatedData = filteredData.slice(offset, offset + limit);

      return {
        data: paginatedData,
        total,
        page: parseInt(page),
        limit
      };
    }

    // 数据库查询
    const { tableName, mapping } = global.tableInfo.idleItems;
    const selectFields = tableAdapter.buildSelectFields(mapping);

    let sql = `SELECT ${selectFields} FROM ${tableName}`;
    let params = [];
    let whereConditions = [];

    if (category && category !== '全部类别' && mapping.itemCategory) {
      whereConditions.push(`${mapping.itemCategory} = ?`);
      params.push(category);
    }
    if (duration && duration !== '' && mapping.borrowDuration) {
      whereConditions.push(`${mapping.borrowDuration} = ?`);
      params.push(parseInt(duration));
    }

    if (whereConditions.length > 0) {
      sql += ` WHERE ${whereConditions.join(' AND ')}`;
    }

    const orderByField = mapping.createTime || 'create_time';
    sql += ` ORDER BY ${orderByField} DESC LIMIT ? OFFSET ?`;
    params.push(limit, offset);

    const rows = await DatabaseService.query(sql, params);
    const transformedData = tableAdapter.transformResult(rows, mapping);
    const total = await DatabaseService.count(tableName, whereConditions, params.slice(0, -2));

    return {
      data: transformedData,
      total,
      page: parseInt(page),
      limit
    };
  });
});

// 发布闲置物品
app.post('/api/idle-items', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const {
      itemName, itemCategory, itemDesc, borrowDuration,
      contactInfo, borrowCondition, imageUrl
    } = req.body;

    const sql = `
      INSERT INTO idle_items
      (item_name, item_category, item_desc, borrow_duration, contact_info, borrow_condition, image_url, status, create_time)
      VALUES (?, ?, ?, ?, ?, ?, ?, 'pending_approval', NOW())
    `;

    const [result] = await pool.execute(sql, [
      itemName, itemCategory, itemDesc, borrowDuration,
      contactInfo, borrowCondition || '', imageUrl || ''
    ]);

    return {
      id: result.insertId,
      itemName, itemCategory, itemDesc, borrowDuration,
      contactInfo, borrowCondition, imageUrl,
      status: 'pending_approval'
    };
  });
});

// 获取技能列表
app.get('/api/skills', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const { page = 1, category, serviceMethod } = req.query;
    const { limit, offset } = DatabaseService.buildPagination(page);

    // 使用模拟数据或数据库查询
    if (!global.tableInfo?.skills) {
      const mockData = await tableAdapter.createMockData(pool, 'services');
      let filteredData = mockData?.skills || [];

      if (category && category !== '全部类别') {
        filteredData = filteredData.filter(skill => skill.category === category);
      }
      if (serviceMethod && serviceMethod !== '') {
        filteredData = filteredData.filter(skill => skill.serviceMethod === serviceMethod);
      }

      const total = filteredData.length;
      const paginatedData = filteredData.slice(offset, offset + limit);

      return {
        data: filteredData,
        total,
        page: parseInt(page),
        limit
      };
    }

    // 数据库查询
    const { tableName, mapping } = global.tableInfo.skills;
    const selectFields = tableAdapter.buildSelectFields(mapping);

    let sql = `SELECT ${selectFields} FROM ${tableName} WHERE ${mapping.status || 'status'} = "approved"`;
    let params = [];

    if (category && category !== '全部类别' && mapping.category) {
      sql += ` AND ${mapping.category} = ?`;
      params.push(category);
    }
    if (serviceMethod && serviceMethod !== '' && mapping.serviceMethod) {
      sql += ` AND ${mapping.serviceMethod} = ?`;
      params.push(serviceMethod);
    }

    const orderByField = mapping.createTime || 'create_time';
    sql += ` ORDER BY ${orderByField} DESC LIMIT ? OFFSET ?`;
    params.push(limit, offset);

    const rows = await DatabaseService.query(sql, params);
    const transformedData = tableAdapter.transformResult(rows, mapping);

    // 获取总数
    let countSql = `SELECT COUNT(*) as total FROM ${tableName} WHERE ${mapping.status || 'status'} = "approved"`;
    let countParams = [];

    if (category && category !== '全部类别' && mapping.category) {
      countSql += ` AND ${mapping.category} = ?`;
      countParams.push(category);
    }
    if (serviceMethod && serviceMethod !== '' && mapping.serviceMethod) {
      countSql += ` AND ${mapping.serviceMethod} = ?`;
      countParams.push(serviceMethod);
    }

    const [countResult] = await pool.execute(countSql, countParams);
    const total = countResult[0].total;

    return {
      data: transformedData,
      total,
      page: parseInt(page),
      limit
    };
  });
});

// 发布技能
app.post('/api/skills', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const {
      name, category, description, serviceMethod,
      contactInfo, serviceTime, needStoreVerification
    } = req.body;

    const status = needStoreVerification ? 'pending_approval' : 'published';

    const sql = `
      INSERT INTO shared_skills
      (name, category, description, service_method, contact_info, service_time, status, create_time)
      VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
    `;

    const [result] = await pool.execute(sql, [
      name, category, description, serviceMethod,
      contactInfo, serviceTime || '', status
    ]);

    return {
      id: result.insertId,
      name, category, description, serviceMethod,
      contactInfo, serviceTime, status
    };
  });
});

// 获取技能详情
app.get('/api/skills/:id', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const { id } = req.params;

    if (!global.tableInfo?.skills) {
      // 返回模拟数据
      const mockData = await tableAdapter.createMockData(pool, 'services');
      return mockData?.skills?.find(skill => skill.id == id) || mockData?.skills?.[0];
    }

    const { tableName, mapping } = global.tableInfo.skills;
    const selectFields = tableAdapter.buildSelectFields(mapping);
    const idField = mapping.id || 'id';

    const sql = `SELECT ${selectFields} FROM ${tableName} WHERE ${idField} = ?`;
    const rows = await DatabaseService.query(sql, [id]);

    if (rows.length === 0) {
      throw new Error('技能不存在');
    }

    // 记录原始查询结果
    console.log('原始查询结果:', rows[0]);

    // 使用table-adapter转换数据
    const transformedData = tableAdapter.transformRow(rows[0], mapping);
    console.log('转换后的数据:', transformedData);
    return transformedData;
  });
});

// 获取闲置物品详情
app.get('/api/idle-items/:id', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const { id } = req.params;

    if (!global.tableInfo?.idleItems) {
      const mockData = await tableAdapter.createMockData(pool, 'services');
      return mockData?.idleItems?.find(item => item.id == id) || mockData?.idleItems?.[0];
    }

    const { tableName, mapping } = global.tableInfo.idleItems;
    const selectFields = tableAdapter.buildSelectFields(mapping);
    const idField = mapping.id || 'id';

    const sql = `SELECT ${selectFields} FROM ${tableName} WHERE ${idField} = ?`;
    const rows = await DatabaseService.query(sql, [id]);

    if (rows.length === 0) {
      throw new Error('物品不存在');
    }

    return tableAdapter.transformResult(rows[0], mapping);
  });
});

// 提交借用请求
app.post('/api/borrow-requests', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const {
      itemId, borrowerName, borrowerPhone, borrowDate,
      returnDate, borrowReason, contactInfo
    } = req.body;

    const sql = `
      INSERT INTO borrow_requests
      (item_id, borrower_name, borrower_phone, borrow_date, return_date, borrow_reason, contact_info, status, create_time)
      VALUES (?, ?, ?, ?, ?, ?, ?, 'pending', NOW())
    `;

    const [result] = await pool.execute(sql, [
      itemId, borrowerName, borrowerPhone, borrowDate,
      returnDate, borrowReason, contactInfo
    ]);

    return {
      id: result.insertId,
      itemId, borrowerName, borrowerPhone, borrowDate,
      returnDate, borrowReason, contactInfo,
      status: 'pending'
    };
  });
});

// 获取公告列表 - 使用模拟数据避免数据库依赖
app.get('/api/notices', (req, res) => {
  const mockNotices = [
    { id: 1, title: '社区安全知识讲座将于本周末举行', date: '2023-11-15' },
    { id: 2, title: '老年人体检预约开始，请符合条件的居民及时报名', date: '2023-11-10' },
    { id: 3, title: '社区服务中心新增多项便民服务，请居民留意通知', date: '2023-11-05' }
  ];

  res.json({
    success: true,
    data: mockNotices
  });
});

// 获取活动列表 - 连接到community_service_platform数据库
app.get('/api/activities', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    // 从community_events表中查询活动数据
    const sql = `
      SELECT
        event_id as id,
        title,
        DATE_FORMAT(event_date, '%Y-%m-%d') as event_date,
        DATE_FORMAT(start_time, '%H:%i') as start_time,
        DATE_FORMAT(end_time, '%H:%i') as end_time,
        location,
        current_participants,
        max_participants
      FROM community_events
      ORDER BY event_date ASC, start_time ASC
    `;

    const rows = await DatabaseService.query(sql);
    return rows;
  });
});

// 获取活动详情 - 连接到community_service_platform数据库
app.get('/api/activities/:id', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const { id } = req.params;
    // 从community_events表中查询活动详情数据
    const sql = `
      SELECT
        event_id as id,
        title,
        description as content,
        organizer as publisher,
        'activity' as notice_type,
        DATE_FORMAT(start_time, '%Y-%m-%d') as date,
        DATE_FORMAT(start_time, '%H:%i') as start_time,
        DATE_FORMAT(end_time, '%H:%i') as end_time,
        location,
        current_participants,
        max_participants,
        cover_image as imageUrl
      FROM community_events
      WHERE event_id = ? AND is_featured = 1
    `;

    const rows = await DatabaseService.query(sql, [id]);
    return rows.length > 0 ? rows[0] : null;
  });
});

// 一键报平安接口
app.post('/api/safety-report', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const {
      userId, date, time, health, note, timestamp
    } = req.body;

    // 验证必填字段
    if (!userId || !date || !time || !health) {
      return {
        success: false,
        message: '缺少必填字段'
      };
    }

    // 检查今日是否已报平安
    const todayCheckQuery = `
      SELECT id FROM safety_reports
      WHERE user_id = ? AND DATE(report_date) = DATE(?)
    `;
    const [existingReports] = await db.execute(todayCheckQuery, [userId, date]);

    if (existingReports.length > 0) {
      return {
        success: false,
        message: '今日已报平安，请勿重复提交'
      };
    }

    // 插入报平安记录
    const insertQuery = `
      INSERT INTO safety_reports (
        user_id, report_date, report_time, health_status,
        note, created_at, updated_at
      ) VALUES (?, ?, ?, ?, ?, NOW(), NOW())
    `;

    const [result] = await db.execute(insertQuery, [
      userId, date, time, health, note || ''
    ]);

    if (result.affectedRows > 0) {
      // 可以在这里添加通知家人或社区管理员的逻辑
      console.log(`用户 ${userId} 报平安成功: ${date} ${time}`);

      return {
        success: true,
        message: '报平安成功',
        data: {
          reportId: result.insertId,
          reportDate: date,
          reportTime: time
        }
      };
    } else {
      throw new Error('报平安失败');
    }
  });
});

// 获取用户报平安历史记录
app.get('/api/safety-report/history/:userId', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const { userId } = req.params;
    const { limit = 30 } = req.query;

    if (!userId) {
      return {
        success: false,
        message: '用户ID不能为空'
      };
    }

    const query = `
      SELECT
        id, user_id,
        DATE_FORMAT(report_date, '%Y-%m-%d') as date,
        TIME_FORMAT(report_time, '%H:%i') as time,
        health_status, note,
        DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') as created_at
      FROM safety_reports
      WHERE user_id = ?
      ORDER BY report_date DESC, report_time DESC
      LIMIT ?
    `;

    const [reports] = await db.execute(query, [userId, parseInt(limit)]);

    return {
      success: true,
      message: '获取历史记录成功',
      data: reports
    };
  });
});

// 检查今日报平安状态
app.get('/api/safety-report/today/:userId', async (req, res) => {
  await APIHandler.handleRequest(res, async () => {
    const { userId } = req.params;

    if (!userId) {
      return {
        success: false,
        message: '用户ID不能为空'
      };
    }

    const query = `
      SELECT
        id, user_id,
        DATE_FORMAT(report_date, '%Y-%m-%d') as date,
        TIME_FORMAT(report_time, '%H:%i') as time,
        health_status, note
      FROM safety_reports
      WHERE user_id = ? AND DATE(report_date) = CURDATE()
      ORDER BY report_time DESC
      LIMIT 1
    `;

    const [todayReports] = await db.execute(query, [userId]);

    return {
      success: true,
      message: '查询成功',
      data: {
        hasReported: todayReports.length > 0,
        report: todayReports.length > 0 ? todayReports[0] : null
      }
    };
  });
});

// 商家相关API接口

// 获取所有商家列表
app.get('/api/merchants', async (req, res) => {
  try {
    const { page = 1, limit = 10 } = req.query;
    const result = await merchantService.getAllMerchants(parseInt(page), parseInt(limit));

    if (result.success) {
      res.json(result);
    } else {
      res.status(400).json(result);
    }
  } catch (error) {
    console.error('获取商家列表失败:', error);
    res.status(500).json({
      success: false,
      message: '获取商家列表失败',
      error: error.message
    });
  }
});

// 获取商家分类列表
app.get('/api/merchants/categories', async (req, res) => {
  try {
    const result = await merchantService.getMerchantCategories();

    if (result.success) {
      res.json(result);
    } else {
      res.status(400).json(result);
    }
  } catch (error) {
    console.error('获取商家分类失败:', error);
    res.status(500).json({
      success: false,
      message: '获取商家分类失败',
      error: error.message
    });
  }
});

// 按分类获取商家
app.get('/api/merchants/category/:category', async (req, res) => {
  try {
    const { category } = req.params;
    const { page = 1, limit = 10 } = req.query;
    const result = await merchantService.getMerchantsByCategory(category, parseInt(page), parseInt(limit));

    if (result.success) {
      res.json(result);
    } else {
      res.status(400).json(result);
    }
  } catch (error) {
    console.error('按分类获取商家失败:', error);
    res.status(500).json({
      success: false,
      message: '按分类获取商家失败',
      error: error.message
    });
  }
});

// 获取商家详情
app.get('/api/merchants/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const result = await merchantService.getMerchantDetail(parseInt(id));

    if (result.success) {
      res.json(result);
    } else {
      res.status(404).json(result);
    }
  } catch (error) {
    console.error('获取商家详情失败:', error);
    res.status(500).json({
      success: false,
      message: '获取商家详情失败',
      error: error.message
    });
  }
});

// 搜索商家
app.get('/api/merchants/search/:keyword', async (req, res) => {
  try {
    const { keyword } = req.params;
    const { page = 1, limit = 10 } = req.query;
    const result = await merchantService.searchMerchants(keyword, parseInt(page), parseInt(limit));

    if (result.success) {
      res.json(result);
    } else {
      res.status(400).json(result);
    }
  } catch (error) {
    console.error('搜索商家失败:', error);
    res.status(500).json({
      success: false,
      message: '搜索商家失败',
      error: error.message
    });
  }
});

// 健康检查接口
app.get('/api/health', (req, res) => {
  res.json({
    success: true,
    message: '服务器运行正常',
    timestamp: new Date().toISOString(),
    uptime: process.uptime(),
    version: '1.0.0'
  });
});

// AI聊天接口
app.post('/api/ai/chat', async (req, res) => {
  try {
    const { skillName, userMessage, context } = req.body;

    if (!skillName || !userMessage) {
      return res.status(400).json({
        success: false,
        message: '缺少必要参数'
      });
    }

    console.log('AI聊天请求:', { skillName, userMessage, context });

    // 调用AI服务
    const result = await aiService.getAIResponse(skillName, userMessage, context);

    if (result.success) {
      res.json({
        success: true,
        data: {
          message: result.data
        }
      });
    } else {
      // 如果AI服务失败，返回备用回复
      res.json({
        success: true,
        data: {
          message: result.fallback
        }
      });
    }
  } catch (error) {
    console.error('AI聊天接口错误:', error);
    res.status(500).json({
      success: false,
      message: 'AI服务暂时不可用',
      error: error.message
    });
  }
});

// 启动服务器
async function startServer() {
  await initializeDatabase();

  app.listen(port, '0.0.0.0', () => {
    console.log(`�� 服务器启动成功，端口: ${port}`);
    console.log(`📡 API地址: http://localhost:${port}/api`);
    console.log(`🌐 网络地址: http://192.168.1.6:${port}/api`);
  });
}

startServer().catch(console.error);